import os, sys, commands, time, re
from datetime import datetime


PARSE_MODE = '-vv --base64-output=decode-rows'


def is_valid_time_str(s):
    """
        check the input str is valid time str or not
        Args:
            s: the input str to be checked
        Returns:
            valid: True
            unvalid: False
    """
    try:
        time.strptime(s, '%Y-%m-%d %H:%M:%S')
        return True
    except:
        return False


def decode_mysqlbinlog(log_file, s_time='', e_time='', binlog_tool=''):
    """
        decode the mysqlbinlog file based on given time period
        Args:
            log_file: the path of mysqlbinlog file
            s_time: the start time point of to be processed logs
                    the argument must be a date and time in the local time zone
                    for example: 2004-12-25 11:25:56
            e_time: the end time point of to be processd logs
                    the argument must be a date and time in the local time zone
                    for example: 2004-12-25 11:25:56
        Returns:
            success: status == 200
            else: fail
    """
    s_time = s_time.replace('T', ' ').replace('Z', '')
    e_time = e_time.replace('T', ' ').replace('Z', '')
    # check args is valid or not
    MYSQLBINLOG_EXE = binlog_tool
    if not os.path.exists(MYSQLBINLOG_EXE):
        return {'status': 601, 'msg': 'MYSQLBINLOG_EXE not exist'}
    if not os.path.exists(log_file):
        return {'status': 602, 'msg': 'input log_file not exist'}
    if (s_time and not is_valid_time_str(s_time)) or (e_time and not is_valid_time_str(e_time)):
        return {'status': 603, 'msg': 's_time or e_time arg is not valid'}

    # build the decoded exe command
    if not s_time and not e_time:
        command = ' '.join([MYSQLBINLOG_EXE, PARSE_MODE, log_file])
    elif s_time and e_time:
        s_time_arg = '--start-datetime="{0}"'.format(s_time)
        e_time_arg = '--stop-datetime="{0}"'.format(e_time)
        command = ' '.join([MYSQLBINLOG_EXE, PARSE_MODE, s_time_arg, e_time_arg, log_file])
    elif s_time:
        s_time_arg = '--start-datetime="{0}"'.format(s_time)
        command = ' '.join([MYSQLBINLOG_EXE, PARSE_MODE, s_time_arg, log_file])
    else:
        e_time_arg = '--stop-datetime="{0}"'.format(e_time)
        command = ' '.join([MYSQLBINLOG_EXE, PARSE_MODE, e_time_arg, log_file])

    # execute the command
    # print command
    (status, output) = commands.getstatusoutput(command)
    if status == 0:
        return {'status': 200, 'msg': 'decode success', 'data': output}
    return {'status': status, 'msg': output}


def parse_mysqlbinlog(log_file, s_time='', e_time='', goal_dbs=[], goal_tables=[], binlog_tool='/usr/bin/mysqlbinlog', tool_version='5.6'):
    """
        input the original mysqlbinlog file, parse it, return the list of wanted sqls
        Args:
            log_file: the path of mysqlbinlog file
            s_time: the start time point of to be processed logs
                    the argument must be a date and time in the local time zone
                    for example: 2004-12-25 11:25:56
            e_time: the end time point of to be processd logs
                    the argument must be a date and time in the local time zone
                    for example: 2004-12-25 11:25:56
            goal_dbs: the db list, you want to focus
            goal_tables: the table list, you want to focus
        Returns:
            fail: {'status' != 200, .....}
            success: sql list
    """
    decode_res = decode_mysqlbinlog(log_file, s_time, e_time, binlog_tool)
    if not s_time:
        s_time = '1970-01-01T01:01:01Z'
    if decode_res.get('status', 0) != 200:
        print decode_res
        # return decode_res
    parsed_binlog = decode_res.get('data', '')
    parsed_binlog_file = 'binlogs/{0}_decoded'.format(os.path.basename(log_file))
    with open(parsed_binlog_file, 'w') as temp_f:
        temp_f.write(parsed_binlog)
    del parsed_binlog
    flag, row_num, sql_lines, sql_type = 0, 0, [], ''
    alter = False
    alter_list = []
    with open(parsed_binlog_file, 'r') as temp_f:

        for line in temp_f:
            # if '1442958379' in line:
            #    import pdb;pdb.set_trace()
            if line.startswith('BEGIN'):
                flag, row_num, sql_lines, sql_type = 1, 1, [], ''
                dbname = None
                tablename = None
            elif flag == 1:
                row_num += 1
                if row_num < 8:
                    # dbname = line.split('Table_map:')[1].split('mapped to')[0].split('.')[0].replace('`', '').strip()
                    try:
                        dbname, tablename = line.split('`')[1], line.split('`')[3]
                    except:
                        continue
                if row_num == 8:
                    if not dbname or not tablename:
                        flag = 0
                        continue
                    else:
                        if (dbname not in goal_dbs) or (tablename not in goal_tables):
                            flag = 0
                            continue

                if line.startswith('###'):
                    line = line[3:].strip()
                    if not sql_type:
                        if line.startswith('UPDATE'):
                            sql_type = 'UPDATE'
                        elif line.startswith('INSERT'):
                            sql_type = 'INSERT'
                        elif line.startswith('DELETE'):
                            sql_type = 'DELETE'
                    if sql_type == "UPDATE":
                        if line.startswith("UPDATE"):
                            if sql_lines:
                                yield {'update': sql_lines}
                            sql_lines = []
                            sql_lines.append("UPDATE")
                        elif line.startswith("@"):
                            if "UPDATE" not in sql_lines:
                                field = re.split(r'\s+/\*', line)[0].split('=', 1)[1]
                                sql_lines.append(field[1:-1] if field.startswith('\'') else field)
                        elif line.startswith("SET"):
                            sql_lines = []
                    elif sql_type == 'DELETE':
                        if line.startswith("DELETE"):
                            if sql_lines:
                                yield {'delete': sql_lines}
                            sql_lines = []
                        elif line.startswith("@"):
                            field = re.split(r'\s+/\*', line)[0].split('=', 1)[1]
                            sql_lines.append(field[1:-1] if field.startswith('\'') else field)
                    elif sql_type == 'INSERT':
                        if line.startswith("INSERT"):
                            if sql_lines:
                                yield {'insert': sql_lines}
                            sql_lines = []
                        elif line.startswith("@"):
                            field = re.split(r'\s+/\*', line)[0].split('=', 1)[1]
                            sql_lines.append(field[1:-1] if field.startswith('\'') else field)
                elif line.startswith('COMMIT'):
                    if sql_lines:
                        yield {sql_type.lower(): sql_lines}
            elif line.startswith('ALTER'):
                if line.find('`%s`' % goal_tables[0]) != -1:

                    alter = True
            elif alter:
                line = line.strip()
                if line.startswith('ADD'):
                    alter_list.append(('add', line.split('`')[1]))
                elif line.startswith('DROP'):
                    alter_list.append(('drop', line.split('`')[1]))
                elif line.startswith('CHANGE'):
                    alter_list.append(('change', line.split('`')[1], line.split('`')[3]))
                elif line.strip() == '/*!*/;':
                    alter = False;
                    yield {'alter': alter_list}
                    alter_list = []
            else:
                m = re.match(r'^#\d+\s+\d+:\d+:\d+', line)
                if m:
                    new_time = m.group(0).replace('#', '20')
                    new_time = datetime.strptime(new_time, '%Y%m%d %H:%M:%S').strftime('%Y-%m-%dT%H:%M:%SZ')
                    if new_time > s_time:
                        s_time = new_time
                continue
    yield {'s_time': s_time}


if __name__ == '__main__':
    # parse_mysqlbinlog('/Users/zhouzheng/Downloads/mysql-bin.000004', '2015-09-22 17:59:12', '2015-09-23 05:46:19')
    sqls = parse_mysqlbinlog('mysql-bin.000042', '2015-10-13 16:07:37', '', ['haizhi'], ['test1'])
    for sql in sqls:
        print sql
